﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.IO;

/// <summary>
/// Summary description for dbConn
/// </summary>
public class dbConn
{
    private static string strIP;
    private static string strPage;
    private static string strPageRef;
    private static string strBrowser;
    private static string strTempBrowser;
    private static string strOS;
    private static string sql, sqlDelete;
    private int i;
    private static StreamWriter ErrorFile;
    private static SqlConnection myConn;

    private static SqlCommand myCmd;

   
    private SqlDataAdapter myDataAdapter;
    private DataSet myDataSet, ds,dsLastName;

	public dbConn()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    public Boolean CheckLogin(string _username, string _password)
    {
        Boolean validlogin = false;
        sql = "select count(*) from [UserAdmin] where UserName = @uname  AND Password = @pword ";
        myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString);
        myCmd = new SqlCommand(sql, myConn);
        myCmd.Parameters.AddWithValue("@uname", _username.ToString());
        myCmd.Parameters.AddWithValue("@pword", _password.ToString());
        try
        {
            myConn.Open();

            if ((int)myCmd.ExecuteScalar() != 0)
            {
                validlogin = true;
            }
            else
            {
                validlogin = false;
            }


        }
        catch (Exception err)
        {
            using (ErrorFile = File.AppendText(System.Web.HttpContext.Current.Request.MapPath("errorLog.txt")))
            {
                ErrorFile.WriteLine(DateTime.Now.ToString() + " : " + err.Message);
            }
            //System.Web.HttpContext.Current.Response.Write(err.Message);
        }
        finally
        {

            myConn.Close();
        }
        return validlogin;
    }

    public DataSet CallViews(string mysql)
    {

        using (myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();
                myDataAdapter = new SqlDataAdapter(mysql, myConn);
                ds = new DataSet();
                myDataAdapter.Fill(ds, "view1");
            }
            catch (Exception err)
            {
                using (ErrorFile = File.AppendText(System.Web.HttpContext.Current.Request.MapPath("errorLog.txt")))
                {
                    ErrorFile.WriteLine(DateTime.Now.ToString() + " : " + err.Message);
                }


                //System.Web.HttpContext.Current.Response.Write(err.Message);

            }
            finally
            {
                myConn.Close();
            }
        }

        return ds;
    }

    public DataSet ViewsByFirst(string fname)
    {

        using (myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();
               
                myDataAdapter = new SqlDataAdapter("CUS_sp_ViewByFirstName", myConn);
                myDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                //Create and add a parameter to Parameters collection for the stored procedure.
                myDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@fname", SqlDbType.VarChar, 40));
                //Assign the search value to the parameter.
                myDataAdapter.SelectCommand.Parameters["@fname"].Value = fname;

                ds = new DataSet();
                myDataAdapter.Fill(ds, "view2");
            }
            catch (Exception err)
            {
                using (ErrorFile = File.AppendText(System.Web.HttpContext.Current.Request.MapPath("errorLog.txt")))
                {
                    ErrorFile.WriteLine(DateTime.Now.ToString() + " : " + err.Message);
                }


                //System.Web.HttpContext.Current.Response.Write(err.Message);

            }
            finally
            {
                myConn.Close();
            }
        }

        return ds;
    }

    public DataSet ViewsByLast(string lname)
    {

        using (myConn = new SqlConnection(ConfigurationManager.ConnectionStrings["prod"].ConnectionString))
        {
            try
            {
                myConn.Open();

                myDataAdapter = new SqlDataAdapter("CUS_sp_ViewByLastName", myConn);
                myDataAdapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                //Create and add a parameter to Parameters collection for the stored procedure.
                myDataAdapter.SelectCommand.Parameters.Add(new SqlParameter("@lname", SqlDbType.VarChar, 40));
                //Assign the search value to the parameter.
                myDataAdapter.SelectCommand.Parameters["@lname"].Value = lname;

                ds = new DataSet();
                myDataAdapter.Fill(ds, "view3");
            }
            catch (Exception err)
            {
                using (ErrorFile = File.AppendText(System.Web.HttpContext.Current.Request.MapPath("errorLog.txt")))
                {
                    ErrorFile.WriteLine(DateTime.Now.ToString() + " : " + err.Message);
                }


                //System.Web.HttpContext.Current.Response.Write(err.Message);

            }
            finally
            {
                myConn.Close();
            }
        }

        return ds;
    }
}
